﻿/**************************************************
 * 文 件 名：DbHelperBase.cs
 * 版    本：V1.0
 * 创 建 人：杨书林
 * 日    期：2011-6-23
 * 说    明：各类DbHelper公共方法的基类

 * 修 改 人：
 * 日    期：
 * 描    述：
 **************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace Ysl.Data.DbHelper
{
    /// <summary>
    /// DbHelperBase
    /// 数据访问类的基类
    /// </summary>
    internal abstract class DbHelperBase : IDisposable
    {
        #region 私有字段
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string ConnectionString = string.Empty;

        /// <summary>
        /// 数据提供工厂
        /// </summary>
        private DbProviderFactory dbProviderFacotry = null;

        /// <summary>
        /// 数据库连接
        /// </summary>
        private DbConnection dbConnection = null;

        /// <summary>
        /// 命令
        /// </summary>
        private DbCommand dbCommand = null;

        /// <summary>
        /// 适配器
        /// </summary>
        private DbDataAdapter dbDataAdapter = null;

        /// <summary>
        /// 事务
        /// </summary>
        private DbTransaction dbTransaction = null;

        /// <summary>
        /// 是否在事务中
        /// </summary>
        private bool InTransaction = false;
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerName">数据提供程序名称</param>
        protected DbHelperBase(string connectionString, string providerName)
        {
            this.ConnectionString = connectionString;
            this.dbProviderFacotry = DbProviderFactories.GetFactory(providerName);
        }
        #endregion

        #region 打开/关闭数据库连接
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        protected IDbConnection OpenConnection()
        {
            //创建数据库连接
            if (this.dbConnection == null)
            {
                this.dbConnection = this.dbProviderFacotry.CreateConnection();
                this.dbConnection.ConnectionString = this.ConnectionString;
            }

            //打开数据库连接
            if (this.dbConnection.State == ConnectionState.Closed)
            {
                this.dbConnection.Open();
            }

            return this.dbConnection;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        protected void CloseConnection()
        {
            if (this.dbConnection != null && this.dbConnection.State == ConnectionState.Open)
            {
                this.dbConnection.Close();
            }
        }
        #endregion

        #region 开始/执行/回滚事务操作
        /// <summary>
        /// 开始事务操作
        /// </summary>
        public void BeginTransaction()
        {
            //打开数据库连接
            this.OpenConnection();

            if (!this.InTransaction)
            {
                //创建事务
                this.dbTransaction = this.dbConnection.BeginTransaction();

                //开始事务，更新标识为true
                this.InTransaction = true;
            }
        }

        /// <summary>
        /// 执行事务操作
        /// </summary>
        public void Commit()
        {
            if (this.InTransaction)
            {
                //执行事务
                this.dbTransaction.Commit();

                //事务完成，更新标识为false
                this.InTransaction = false;

                //事务完成，关闭连接
                this.CloseConnection();
            }
        }

        /// <summary>
        /// 回滚事务操作
        /// </summary>
        public void Rollback()
        {
            if (this.InTransaction)
            {
                //回滚事务
                this.dbTransaction.Rollback();

                //事务回滚，更新标识为false
                this.InTransaction = false;

                //事务回滚，关闭连接
                this.CloseConnection();
            }
        }
        #endregion

        #region 组织参数及相关方法
        /// <summary>
        /// 参数名称前加参数标识符
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <returns>返回string类型</returns>
        public abstract string AddParameterToken(string parameterName);

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="parameterValue">值</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeInParameter(string parameterName, object parameterValue)
        {
            IDbDataParameter dbParameter = null;
            if (!string.IsNullOrEmpty(parameterName))
            {
                dbParameter = dbProviderFacotry.CreateParameter();
                dbParameter.ParameterName = this.AddParameterToken(parameterName);
                dbParameter.Value = parameterValue;
            }

            return dbParameter;
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="parameterValue">值</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeInParameter(string parameterName, DbType dbType, object parameterValue)
        {
            return this.MakeInParameter(parameterName, dbType, 0, parameterValue);
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">类型长度</param>
        /// <param name="parameterValue">值</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeInParameter(string parameterName, DbType dbType, int size, object parameterValue)
        {
            return this.MakeParameter(parameterName, dbType, size, ParameterDirection.Input, parameterValue);
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">类型长度</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeOutParameter(string parameterName, DbType dbType, int size)
        {
            return this.MakeParameter(parameterName, dbType, size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">类型长度</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeReturnParameter(string parameterName, DbType dbType, int size)
        {
            return this.MakeParameter(parameterName, dbType, size, ParameterDirection.ReturnValue, null);
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">类型长度</param>
        /// <param name="parameterDirection">参数的输入、输出类型</param>
        /// <param name="parameterValue">值</param>
        /// <returns>返回IDbDataParameter类型</returns>
        public virtual IDbDataParameter MakeParameter(string parameterName, DbType dbType, int size, ParameterDirection parameterDirection, object parameterValue)
        {
            IDbDataParameter dbParameter = null;
            if (!string.IsNullOrEmpty(parameterName))
            {
                dbParameter = dbProviderFacotry.CreateParameter();
                dbParameter.ParameterName = this.AddParameterToken(parameterName);
                dbParameter.DbType = dbType;
                if (size > 0)
                {
                    dbParameter.Size = size;
                }
                dbParameter.Direction = parameterDirection;
                if (!(parameterDirection == ParameterDirection.Output || parameterValue == null))
                {
                    dbParameter.Value = parameterValue;
                }
            }

            return dbParameter;
        }

        /// <summary>
        /// 根据参数名和值组织参数
        /// </summary>
        /// <param name="parameterNames">参数名称</param>
        /// <param name="parameterValues">值</param>
        /// <returns>返回IDbDataParameter[]类型</returns>
        public virtual IDbDataParameter[] MakeInParameters(string[] parameterNames, params object[] parameterValues)
        {
            if (parameterNames == null || parameterValues == null)
                return null;

            if (parameterNames.Length <= 0 || parameterValues.Length <= 0 || parameterNames.Length != parameterValues.Length)
                return null;

            //这里需要用泛型列表，因为不能创建IDbDataParameter类型的数组
            List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
            for (int i = 0; i < parameterNames.Length; i++)
            {
                dbParameters.Add(this.MakeInParameter(parameterNames[i], parameterValues[i]));
            }

            return dbParameters.ToArray();

        }

        /// <summary>
        /// 根据存储过程和值组织参数
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameterValues">值</param>
        /// <returns>返回IDbDataParameter[]类型</returns>
        public virtual IDbDataParameter[] MakeProcedureParameters(string procedureName, object[] parameterValues)
        {
            IDbDataParameter[] dbParameters = this.GetProcedureParameters(procedureName);
            this.AssignParameters(dbParameters, parameterValues);

            return dbParameters;
        }

        /// <summary>
        /// 获取存储过程参数集（不包括返回值参数）
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回IDbDataParameter[]类型</returns>
        public virtual IDbDataParameter[] GetProcedureParameters(string procedureName)
        {
            IDbDataParameter[] dbParameters = this.GetProcedureParameters(procedureName, false);

            return dbParameters;
        }

        /// <summary>
        /// 获取存储过程参数集
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数（true表示包含，false表示不包含，默认为false）</param>
        /// <returns>返回IDbDataParameter[]类型</returns>
        public abstract IDbDataParameter[] GetProcedureParameters(string procedureName, bool includeReturnValueParameter);

        /// <summary>
        /// 为参数赋值
        /// </summary>
        /// <param name="dbParameters">参数</param>
        /// <param name="parameterValues">值</param>
        public virtual void AssignParameters(IDbDataParameter[] dbParameters, object[] parameterValues)
        {
            if (dbParameters == null || parameterValues == null)
                return;

            if (dbParameters.Length <= 0 || parameterValues.Length <= 0 || dbParameters.Length < parameterValues.Length)
                return;

            int j = 0;  //保存参数值的索引值

            //循环查找参数dbParameters类型为ParameterDirection.Input或ParameterDirection.InputOutput的参数并赋值
            for (int i = 0; i < dbParameters.Length; i++)
            {
                if (dbParameters[i].Direction == ParameterDirection.Input || dbParameters[i].Direction == ParameterDirection.InputOutput)
                {
                    if (parameterValues[j] is IDbDataParameter)
                    {
                        IDbDataParameter param = (IDbDataParameter)parameterValues[j];
                        if (param.Value == null)
                        {
                            dbParameters[i].Value = DBNull.Value;
                        }
                        else
                        {
                            dbParameters[i].Value = param.Value;
                        }
                    }
                    else if (parameterValues[j] == null)
                    {
                        dbParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        dbParameters[i].Value = parameterValues[j];
                    }

                    j++;
                }
            }
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 对连接执行SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuerySql(string sqlText)
        {
            return this.ExecuteNonQuerySql(sqlText, null);
        }

        /// <summary>
        /// 对连接执行SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuerySql(string sqlText, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteNonQuery(CommandType.Text, sqlText, dbParameters);
        }

        /// <summary>
        /// 对连接执行存储过程并返回受影响的行数
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQueryProcedure(string procedureName)
        {
            return this.ExecuteNonQueryProcedure(procedureName, null);
        }

        /// <summary>
        /// 对连接执行存储过程并返回受影响的行数
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQueryProcedure(string procedureName, params object[] parameterValues)
        {
            if (parameterValues is IDbDataParameter[])
            {
                return this.ExecuteNonQuery(CommandType.StoredProcedure, procedureName, (IDbDataParameter[])parameterValues);
            }
            else
            {
                IDbDataParameter[] dbParameters = this.MakeProcedureParameters(procedureName, parameterValues);

                return this.ExecuteNonQuery(CommandType.StoredProcedure, procedureName, dbParameters);
            }
        }

        /// <summary>
        /// 对链接执行操作并返回受影响的行数
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return this.ExecuteNonQuery(commandType, commandText, null);
        }

        /// <summary>
        /// 对链接执行操作并返回受影响的行数
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuery(CommandType commandType, string commandText, params IDbDataParameter[] dbParameters)
        {
            //打开连接
            if (this.dbConnection == null || this.dbConnection.State != ConnectionState.Open)
            {
                this.OpenConnection();
            }

            //创建DbCommand命令
            this.dbCommand = this.dbConnection.CreateCommand();
            this.dbCommand.Connection = this.dbConnection;
            this.dbCommand.CommandType = commandType;
            this.dbCommand.CommandText = commandText;

            if (this.InTransaction)
            {
                //如果在事务中，添加事务操作
                this.dbCommand.Transaction = this.dbTransaction;
            }

            //组织参数
            if (dbParameters != null)
            {
                //this.dbCommand.Parameters.Clear();
                foreach (IDbDataParameter parameter in dbParameters)
                {
                    this.dbCommand.Parameters.Add(parameter);
                }
            }

            //执行命令操作
            int returnValue = this.dbCommand.ExecuteNonQuery();

            if (!this.InTransaction)
            {
                //如果不在事务中，执行完操作后关闭连接
                this.CloseConnection();
            }

            return returnValue;
        }
        #endregion

        #region ExecuteFill
        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillSql(string sqlText)
        {
            return this.ExecuteFillSql(sqlText, string.Empty);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillSql(string sqlText, string tableName)
        {
            return this.ExecuteFillSql(sqlText, tableName, null);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillSql(string sqlText, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteFillSql(sqlText, string.Empty, dbParameters);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="tableName">表名</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillSql(string sqlText, string tableName, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteFill(CommandType.Text, sqlText, tableName, dbParameters);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="procedureName">存储过程</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillProcedure(string procedureName)
        {
            return this.ExecuteFillProcedure(procedureName, string.Empty);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="procedureName">存储过程</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillProcedure(string procedureName, string tableName)
        {
            return this.ExecuteFillProcedure(procedureName, tableName, null);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="procedureName">存储过程</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillProcedure(string procedureName, params object[] parameterValues)
        {
            return this.ExecuteFillProcedure(procedureName, string.Empty, parameterValues);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="procedureName">存储过程</param>
        /// <param name="tableName">表名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFillProcedure(string procedureName, string tableName, params object[] parameterValues)
        {
            if (parameterValues is IDbDataParameter[])
            {
                return this.ExecuteFill(CommandType.StoredProcedure, procedureName, tableName, (IDbDataParameter[])parameterValues);
            }
            else
            {
                IDbDataParameter[] dbParameters = this.MakeProcedureParameters(procedureName, parameterValues);

                return this.ExecuteFill(CommandType.StoredProcedure, procedureName, tableName, dbParameters);
            }
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFill(CommandType commandType, string commandText, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteFill(commandType, commandText, null, dbParameters);
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回数据表</returns>
        public virtual DataTable ExecuteFill(CommandType commandType, string commandText, string tableName, params IDbDataParameter[] dbParameters)
        {
            //打开连接
            if (this.dbConnection == null || this.dbConnection.State != ConnectionState.Open)
            {
                this.OpenConnection();
            }

            //创建DbCommand命令
            this.dbCommand = this.dbConnection.CreateCommand();
            this.dbCommand.Connection = this.dbConnection;
            this.dbCommand.CommandType = commandType;
            this.dbCommand.CommandText = commandText;

            if (this.InTransaction)
            {
                //如果在事务中，添加事务操作
                this.dbCommand.Transaction = this.dbTransaction;
            }

            //创建适配器
            this.dbDataAdapter = this.dbProviderFacotry.CreateDataAdapter();
            this.dbDataAdapter.SelectCommand = this.dbCommand;

            //组织参数
            if (dbParameters != null)
            {
                //this.dbCommand.Parameters.Clear();
                foreach (IDbDataParameter parameter in dbParameters)
                {
                    this.dbCommand.Parameters.Add(parameter);
                }
            }

            DataTable dataTable = null;
            if (!string.IsNullOrEmpty(tableName))
            {
                dataTable = new DataTable(tableName);
            }
            else
            {
                dataTable = new DataTable();
            }

            //执行适配器填充数据
            this.dbDataAdapter.Fill(dataTable);

            if (!this.InTransaction)
            {
                //如果不在事务中，执行完操作后关闭连接
                this.CloseConnection();
            }

            return dataTable;
        }


        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataSet">数据集</param>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        public virtual void ExecuteFill(DataSet dataSet, CommandType commandType, string commandText)
        {
            this.ExecuteFill(dataSet, commandType, commandText, string.Empty, null);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataSet">数据集</param>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="dbParameters">参数</param>
        public virtual void ExecuteFill(DataSet dataSet, CommandType commandType, string commandText, params IDbDataParameter[] dbParameters)
        {
            this.ExecuteFill(dataSet, commandType, commandText, null, dbParameters);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataSet">数据集</param>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dbParameters">参数</param>
        public virtual void ExecuteFill(DataSet dataSet, CommandType commandType, string commandText, string tableName, params IDbDataParameter[] dbParameters)
        {
            DataTable dataTable = this.ExecuteFill(commandType, commandText, tableName, dbParameters);
            dataSet.Tables.Add(dataTable);
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReaderSql(string sqlText)
        {
            return this.ExecuteReaderSql(sqlText, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReaderSql(string sqlText, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteReader(CommandType.Text, sqlText, dbParameters);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReaderProcedure(string procedureName)
        {
            return this.ExecuteReaderProcedure(procedureName, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReaderProcedure(string procedureName, params object[] parameterValues)
        {
            if (parameterValues is IDbDataParameter[])
            {
                return this.ExecuteReader(CommandType.StoredProcedure, procedureName, (IDbDataParameter[])parameterValues);
            }
            else
            {
                IDbDataParameter[] dbParameters = this.MakeProcedureParameters(procedureName, parameterValues);

                return this.ExecuteReader(CommandType.StoredProcedure, procedureName, dbParameters);
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return this.ExecuteReader(commandType, commandText, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回IDataReader类型</returns>
        public virtual IDataReader ExecuteReader(CommandType commandType, string commandText, params IDbDataParameter[] dbParameters)
        {
            //打开连接
            if (this.dbConnection == null || this.dbConnection.State != ConnectionState.Open)
            {
                this.OpenConnection();
            }

            //创建DbCommand命令
            this.dbCommand = this.dbConnection.CreateCommand();
            this.dbCommand.Connection = this.dbConnection;
            this.dbCommand.CommandType = commandType;
            this.dbCommand.CommandText = commandText;

            if (this.InTransaction)
            {
                //如果在事务中，添加事务操作
                this.dbCommand.Transaction = this.dbTransaction;
            }

            //组织参数
            if (dbParameters != null)
            {
                //this.dbCommand.Parameters.Clear();
                foreach (IDbDataParameter parameter in dbParameters)
                {
                    this.dbCommand.Parameters.Add(parameter);
                }
            }

            //执行命令操作
            IDataReader reader = null;
            if (!this.InTransaction)
            {
                //如果不在事务中，当关闭DataReader对象时，关联的Connection对象也将关闭
                reader = this.dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                //this.CloseConnection();
            }
            else
            {
                reader = this.dbCommand.ExecuteReader();
            }

            return reader;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalarSql(string sqlText)
        {
            return this.ExecuteScalarSql(sqlText, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalarSql(string sqlText, params IDbDataParameter[] dbParameters)
        {
            return this.ExecuteScalar(CommandType.Text, sqlText, dbParameters);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalarProcedure(string procedureName)
        {
            return this.ExecuteScalarProcedure(procedureName, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalarProcedure(string procedureName, params object[] parameterValues)
        {
            if (parameterValues is IDbDataParameter[])
            {
                return this.ExecuteScalar(CommandType.StoredProcedure, procedureName, (IDbDataParameter[])parameterValues);
            }
            else
            {
                IDbDataParameter[] dbParameters = this.MakeProcedureParameters(procedureName, parameterValues);

                return this.ExecuteScalar(CommandType.StoredProcedure, procedureName, dbParameters);
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalar(CommandType commandType, string commandText)
        {
            return this.ExecuteScalar(commandType, commandText, null);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="commandType">命令字符串类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>返回object类型</returns>
        public virtual object ExecuteScalar(CommandType commandType, string commandText, params IDbDataParameter[] dbParameters)
        {
            //打开连接
            if (this.dbConnection == null || this.dbConnection.State != ConnectionState.Open)
            {
                this.OpenConnection();
            }

            //创建DbCommand命令
            this.dbCommand = this.dbConnection.CreateCommand();
            this.dbCommand.Connection = this.dbConnection;
            this.dbCommand.CommandType = commandType;
            this.dbCommand.CommandText = commandText;

            if (this.InTransaction)
            {
                //如果在事务中，添加事务操作
                this.dbCommand.Transaction = this.dbTransaction;
            }

            //组织参数
            if (dbParameters != null)
            {
                //this.dbCommand.Parameters.Clear();
                foreach (IDbDataParameter parameter in dbParameters)
                {
                    this.dbCommand.Parameters.Add(parameter);
                }
            }

            //执行命令操作
            object returnValue = this.dbCommand.ExecuteScalar();

            if (!this.InTransaction)
            {
                //如果不在事务中，执行完操作后关闭连接
                this.CloseConnection();
            }

            return returnValue;
        }
        #endregion

        #region Dispose
        /// <summary>
        /// 释放分配的资源的方法
        /// </summary>
        public void Dispose()
        {
            if (this.dbConnection != null)
            {
                this.dbConnection.Close();
                this.dbConnection.Dispose();
            }
            this.dbConnection = null;
            this.dbCommand = null;
            this.dbDataAdapter = null;
            this.dbTransaction = null;
        }
        #endregion
    }
}
